﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Project.Co_DataTransformationPrototype
{
    public class KeyUpdatedMapping : MappingBase
    {
        private Entity foreignKeyReference;
        private bool attributeIsPrimaryKey;
        private bool constraintIsSetToTrue;
        private string strOldEntityName;
        private string strOldAttributeName;

        public KeyUpdatedMapping(EventContext eventContext, bool attributeIsPrimaryKey, bool constraintIsSetToTrue, Entity refenceEntity = null, string oldNameOfEntity = null, string oldNameOfAttribute = null)
        {
            this.eventContext = eventContext;
            this.attributeIsPrimaryKey = attributeIsPrimaryKey;
            this.constraintIsSetToTrue = constraintIsSetToTrue;
            this.foreignKeyReference = refenceEntity;
            this.strOldAttributeName = oldNameOfAttribute;
            this.strOldEntityName = oldNameOfEntity;
            //generateSQLCodeMapping();
        }

        internal override void generateSQLCodeMapping(StringBuilder existingScript = null)
        {
            strSQLMapping = new StringBuilder();

            if (existingScript != null)
                strSQLMapping.Append(existingScript);

            setKeyUpdatedSQLCode();

            strSQLMapping.AppendLine("GO" + Environment.NewLine);
        }

        private void setKeyUpdatedSQLCode()
        {
            Entity parentEntity;
            EntityAttribute attribute = eventContext.elementEventArgs.ModelElement as EntityAttribute;
            string strSQL = @"/*** --- Updating key contraint for table {0} on column {1} --- ***/" + Environment.NewLine;

            if (attribute.IsDeleted)
                parentEntity = attribute.ParentEntity;
            else
                parentEntity = attribute.Entity;

            strSQLMapping.AppendFormat(strSQL, parentEntity.CurrentName, attribute.CurrentName);

            if (attributeIsPrimaryKey)
            {
                if (!constraintIsSetToTrue)
                {
                    strSQLMapping.Append(@"/*** --- Attribute was primary key --- ***/" + Environment.NewLine);
                    strSQLMapping.AppendFormat(@"ALTER TABLE [{0}].{1}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName);
                    if (String.IsNullOrEmpty(strOldAttributeName))
                        strSQLMapping.AppendFormat(@"    DROP CONSTRAINT PK_{0};" + Environment.NewLine, attribute.CurrentName);
                    else
                        strSQLMapping.AppendFormat(@"    DROP CONSTRAINT PK_{0};" + Environment.NewLine, strOldAttributeName);
                }
                else
                {
                    strSQLMapping.Append(@"/*** --- Attribute is now primary key --- ***/" + Environment.NewLine);
                    // Make sure that the column is not nullable
                    strSQLMapping.AppendLine(@"DECLARE @intCounter int");
                    strSQLMapping.AppendLine(String.Format(@"SET @intCounter = COLUMNPROPERTY(OBJECT_ID('{0}.{1}', 'U'), '{2}', 'AllowsNull')", Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName, attribute.CurrentName));
                    strSQLMapping.AppendLine(@"IF  @intCounter = 1");
                    strSQLMapping.AppendLine(String.Format(@"ALTER TABLE [{0}].[{1}]", Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName));
                    strSQLMapping.AppendLine(String.Format(@"    ALTER COLUMN {0} {1} NOT NULL", attribute.CurrentName, MappingTypeConvertion.translateDataTypeIntoSQLString(attribute.Type)));
                    strSQLMapping.AppendLine("GO");
                    // Apply PK constraint
                    strSQLMapping.AppendFormat(@"ALTER TABLE [{0}].{1}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName);
                    strSQLMapping.AppendFormat(@"    ADD CONSTRAINT PK_{0}" + Environment.NewLine, attribute.CurrentName);
                    strSQLMapping.AppendFormat(@"        PRIMARY KEY ({0});" + Environment.NewLine, attribute.CurrentName);
                }
            }
            else
            {
                if (!constraintIsSetToTrue)
                {
                    strSQLMapping.Append(@"/*** --- Attribute was foreign key --- ***/" + Environment.NewLine);
                    strSQLMapping.AppendFormat(@"ALTER TABLE [{0}].{1}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName);
                    if (!String.IsNullOrEmpty(strOldEntityName))
                        strSQLMapping.AppendFormat(@"    DROP CONSTRAINT FK_{0}_{1};" + Environment.NewLine, strOldEntityName, attribute.CurrentName);
                    else if (!String.IsNullOrEmpty(strOldAttributeName))
                        strSQLMapping.AppendFormat(@"    DROP CONSTRAINT FK_{0}_{1};" + Environment.NewLine, parentEntity.CurrentName, strOldAttributeName);
                    else
                        strSQLMapping.AppendFormat(@"    DROP CONSTRAINT FK_{0}_{1};" + Environment.NewLine, parentEntity.CurrentName, attribute.CurrentName);
                }
                else
                {
                    // TODO: what happens if foreignKeyReference is not set?
                    System.Diagnostics.Debug.Assert(foreignKeyReference != null, "Referenced Entity not present - KeyUpdateMapping");

                    strSQLMapping.Append(@"/*** --- Attribute is now foreign key --- ***/" + Environment.NewLine);
                    strSQLMapping.AppendFormat(@"ALTER TABLE [{0}].{1}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName);
                    strSQLMapping.AppendFormat(@"    ADD CONSTRAINT FK_{0}_{1}" + Environment.NewLine, parentEntity.CurrentName, attribute.CurrentName);
                    strSQLMapping.AppendFormat(@"        FOREIGN KEY ({0})" + Environment.NewLine, attribute.CurrentName);
                    strSQLMapping.AppendFormat(@"        REFERENCES {0}", foreignKeyReference.CurrentName);
                    strSQLMapping.Append(getCascadeMechanismInfo(foreignKeyReference, parentEntity) + Environment.NewLine);
                }
            }
        }

        private string getCascadeMechanismInfo(Entity sourceEntity, Entity destinationEntity)
        {
            string sqlString = String.Empty, result = ";";

            EntityAssociation link = EntityAssociation.GetLink(sourceEntity, destinationEntity);
            if (link != null)
            {
                sqlString = @"        ";
                if (link.DoCascadeDelete)
                    sqlString += "ON DELETE CASCADE ";
                if (link.DoCascadeUpdate)
                    sqlString += "ON UPDATE CASCADE ";

                if (!sqlString.Equals(@"        "))
                    result = Environment.NewLine + sqlString + ";";
            }

            return result;
        }
    }
}
